﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Security.Cryptography;
using Npgsql;




namespace Emploees
{

    public partial class WorkTimeForm : Form
    {
        
        private DataSet ds = new DataSet();
        private DataTable dt = new DataTable();
        private DataTable dt1 = new DataTable();
        private static string DateNow = DateTime.Now.Date.ToString("yyy-MM-d");
        
        //static string sql1 = "select \"id_workers\",\"last_data\" from \"WorkTime\".\"Workers\",\"WorkTime\".\"Employee\" where \"id_department\"=@a and \"id_workers\"=\"id_name\"";
        static string sql1 = "select \"id_workers\",\"last_data\" from \"WorkTime\".\"Workers\" INNER JOIN \"WorkTime\".\"Employee\" ON \"id_department\"=@a and \"id_workers\"=\"id_name\"";

       
        static string sql4 = "select * from \"WorkTime\".\"WorkTimes\" where \"Data\"=@a and id_name=@b";
        static string sql5 = "select * from \"WorkTime\".\"WorkTimes\" where \"EndWork\" is not null and id_name=@b and \"Data\"=@a";
        DateTime curTime = DateTime.Now;
        //CultureInfo provider = CultureInfo.InvariantCulture;
        //private NpgsqlDate DateNow = Npgslq
        //NpgsqlConnection conn = new NpgsqlConnection("Server=10.1.0.241;Port=5432;User Id=postgres;Password=system;Database=Staff;");
        //Unix asd = new Unix();
        static public NpgsqlConnection conn = Global_variables.connecttodb();

                
        public WorkTimeForm()
        {
            
            InitializeComponent();
           
            try
            {
                NpgsqlDataAdapter da1 = new NpgsqlDataAdapter(sql1, conn);
                da1.SelectCommand.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Integer));
                da1.SelectCommand.Parameters[0].Value = Global_variables.id_department;
                conn.Open();
               DataSet MyDataSet = new DataSet();
               //MessageBox.Show(dateTimePicker1.Value.ToString());
               //dateTimePicker1.Hide();
               dateTimePicker1.ShowCheckBox = true;
               //dateTimePicker1.ShowUpDown = false;
               DateTime data = dateTimePicker1.Value.Date;
               string str = data.Date.ToString("yyyy/MM/dd");
               DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
               dtfi.ShortDatePattern = "dd-MM-yyyy";
               dtfi.DateSeparator = "-";
               DateTime data1 = Convert.ToDateTime(str,dtfi);
               string enteredVal = DateTime.Now.ToString("dd/MM/yyyy hh:mm:ss tt");
               string dt5 = DateTime.Now.ToString("HH-mm-ss");
               string dt6 = DateTime.Now.ToString();
               
               MessageBox.Show(dt6.ToString());
               IFormatProvider theCultureInfo1 = new System.Globalization.CultureInfo("en-US", true);
               
               //string dt6 = DateTime.Now.ToShortTimeString();
               string dt7 = DateTime.Now.ToString("MM-dd-yyyy");
               DateTime theDateTime = DateTime.ParseExact(dt7, "MM-dd-yyyy", theCultureInfo1);
               MessageBox.Show(dt7.ToString());

                da1.Fill(MyDataSet,"Workers");

                MyDataSet.Tables["Workers"].Columns.Add("ID", typeof(string), "id_workers");
                cmbBox1.DataSource = MyDataSet.Tables["Workers"];
                cmbBox1.ValueMember = "id_workers";
                cmbBox1.DisplayMember = "last_data";
                
                /*
                NpgsqlCommand command4 = new NpgsqlCommand(sql4, conn);
                command4.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Date));
                command4.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Integer));
                command4.Parameters[0].Value = Convert.ToDateTime(DateNow);
                command4.Parameters[1].Value = Convert.ToInt32(Convert.ToString(cmbBox1.SelectedValue));
                int count = 0;
                NpgsqlDataReader res = command4.ExecuteReader();
                while (res.Read()) count++;
                if (0 != count)
                {
                    btnStartWork.Enabled = false;
                    
                    if (qqq(this, sql5) != 0)
                    {
                        btnEndWork.Enabled = false;
                        btnStartWork.Enabled = false;
                    }
                    else
                    {
                        btnEndWork.Enabled = true;
                        btnStartWork.Enabled = false;
                    }

                   
                }
                
                qqq2(this, sql);
                
                /*
                string sql5 = "select * from \"WorkTime\".\"WorkTimes\" where \"EndWork\"=@a and id_name=@b";
                NpgsqlCommand command5 = new NpgsqlCommand(sql5, conn);
                command5.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Text));
                command5.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Integer));
                command5.Parameters[0].Value = Convert.ToDateTime(DateNow);
                command5.Parameters[1].Value = Convert.ToInt32(Convert.ToString(cmbBox1.SelectedValue));
                count = 0;
                res = command5.ExecuteReader();
                while (res.Read()) count++;
                if (0 != count)
                {
                    btnEndWork.Enabled = false;
                }
                else btnEndWork.Enabled = true;
                */
                //textBox1.Text = DateNow;
                /*
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    cmbBox1.Items.Add(dt1.Rows[i]["Name"]);
                    
                }
                cmbBox1.SelectedIndex = 0;
                */
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }

            finally
            {
               // conn.Close();
            }
        }

        private void btExit_Click(object sender, EventArgs e)
        {
            conn.Close();
            Application.Exit();
        }

        static int qqq(WorkTimeForm form, string sql)
        {
            int count = 0;
            //string sql4 = "select * from \"WorkTime\".\"WorkTimes\" where \"Data\"=@a and id_name=@b";
            NpgsqlCommand command4 = new NpgsqlCommand(sql, conn);
            command4.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Date));
            command4.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Integer));
            command4.Parameters[0].Value = Convert.ToDateTime(DateNow);
            command4.Parameters[1].Value = Convert.ToInt32(Convert.ToString(form.cmbBox1.SelectedValue));
            NpgsqlDataReader res = command4.ExecuteReader();
            while (res.Read()) count++;
            return count;
        }

        static int qqq1(WorkTimeForm form, string sql)
        {
            int count = 0;
            NpgsqlCommand command5 = new NpgsqlCommand(sql, conn);
            command5.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Integer));
            command5.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Date));
            command5.Parameters[0].Value = Convert.ToInt32(Convert.ToString(form.cmbBox1.SelectedValue));
            command5.Parameters[1].Value = Convert.ToDateTime(DateNow);
            NpgsqlDataReader res1 = command5.ExecuteReader();
            while (res1.Read()) count++;
            return count;
        }

         void qqq2(WorkTimeForm form, string sql)
        {
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            da.SelectCommand.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Integer));
            da.SelectCommand.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Date));
            da.SelectCommand.Parameters[0].Value = int.Parse(form.cmbBox1.SelectedValue.ToString());
            da.SelectCommand.Parameters[1].Value = Convert.ToDateTime(DateNow);

            form.ds.Reset();
            da.Fill(form.ds);
            form.dt = form.ds.Tables[0];
            print_dgview(this, dt);
            /*
            form.dgview1.DataSource = form.dt;

            form.textBox1.Text = form.cmbBox1.Text;

            form.dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";
            form.dgview1.Columns["Name"].Width = 110;

            form.dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderText = "Дата";
            form.dgview1.Columns["Data"].Width = 80;

            form.dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderText = "Начало";
            form.dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["StartWork"].Width = 50;

            form.dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderText = "Окончание";
            form.dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["EndWork"].Width = 60;
             */ 
        }

        void qqq3(WorkTimeForm form, string sql)
        {
            // conn.Open(); //opens the connection
            //string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name";
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
            // i always reset DataSet before i do something with it.... i don't know why :-)
            form.ds = new DataSet();
            form.ds.Reset();
            // filling DataSet with result from NpgsqlDataAdapter

            da.Fill(form.ds);
            // since it C# DataSet can handle multiple tables, we will select first
            form.dt = form.ds.Tables[0];

            //dgview1.AllowUserToAddRows = false;
            //dgview1.Columns[0].DefaultCellStyle.Format = "HH:mm";
            //dgview1.Columns[2].Name = "Asdf";
            //dgview1.DefaultCellStyle.Format = "HH:mm";

            // connect grid to DataTable
            //DataGridViewTextBoxColumn buttonColumn = new DataGridViewTextBoxColumn();
            DataColumn dc = new DataColumn("Item", typeof(String));
            dc.ColumnName = "Item";
            // Columns["Разность"].
            form.dt.Columns.Add(dc);
            //MessageBox.Show(Convert.ToString(dt.Columns[4].DataType));


            foreach (DataRow row in form.dt.Rows)
            {
                DateTime dateTime1 = Convert.ToDateTime(row.ItemArray[2]);
                //int secondStartWork = dateTime1.Second;
                int hourStartWork = dateTime1.Hour;
                //MessageBox.Show(Convert.ToString(time1));
                 //dateTime2 = new DateTime();
                if (row.ItemArray[3] != DBNull.Value)
                {
                    DateTime dateTime2 = Convert.ToDateTime(row.ItemArray[3]);
                    //int secondEndWork = dateTime2.Second;    
                    int hourEndWork = dateTime2.Hour;
                    
                    TimeSpan aaa = TimeSpan.Zero;
                    aaa = dateTime2 - dateTime1; 
                    if (hourStartWork - hourEndWork == 0)
                    {
                        int minuteStartWork = dateTime1.Minute;
                        int minuteEndWork = dateTime2.Minute;
                        //if (secondEndWork + (60 - secondStartWork) >= 60) minuteEndWork += 1;
                        row["Item"] = string.Format("{0:00}:{1:00}", Math.Floor(((decimal)(minuteEndWork-minuteStartWork)) / 60), Math.Abs(minuteEndWork - minuteStartWork));  // (TimeSpan)(dateTime2 - dateTime1);
                    }
                    else
                    {
                        int minuteStartWork = 60 - dateTime1.Minute;
                        int minuteEndWork = dateTime2.Minute;
                        //if (secondEndWork + (60 - secondStartWork) >= 60) minuteEndWork += 1;
                        if ((minuteStartWork + minuteEndWork)>60)
                        {
                            row["Item"] = string.Format("{0:00}:{1:00}", Math.Floor(aaa.TotalMinutes / 60), (minuteStartWork + minuteEndWork)-60);
                        }
                        else
                        row["Item"] = string.Format("{0:00}:{1:00}", Math.Floor(aaa.TotalMinutes / 60), minuteStartWork + minuteEndWork);  // (TimeSpan)(dateTime2 - dateTime1);
                    }
                }

            }
            string sortString = "[Name] asc, [Data]  asc";
            form.dt.DefaultView.Sort = sortString;

            /*

            form.dgview1.AutoGenerateColumns = true;
            form.dgview1.DataSource = form.dt;


            form.dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";
            form.dgview1.Columns["Name"].Width = 110;

            form.dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderText = "Дата";
            form.dgview1.Columns["Data"].Width = 80;

            form.dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderText = "Начало";
            form.dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["StartWork"].Width = 50;

            form.dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderText = "Окончание";
            form.dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["EndWork"].Width = 60;
            */
            print_dgview(this, dt);

            form.dgview1.Columns["Item"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Item"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Item"].HeaderText = "Время работы час:мин";
            form.dgview1.Columns["Item"].Width = 50;




            form.dgview1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            form.dgview1.AllowUserToResizeColumns = false;
        }
        void print_dgview(WorkTimeForm form, DataTable dt)
        {
            form.dgview1.DataSource = form.dt;

            form.textBox1.Text = form.cmbBox1.Text;
            
            form.dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";
            form.dgview1.Columns["Name"].Width = 110;

            form.dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["Data"].HeaderText = "Дата";
            form.dgview1.Columns["Data"].Width = 65;

            form.dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["StartWork"].HeaderText = "Начало работы";
            form.dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["StartWork"].Width = 65;

            form.dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            form.dgview1.Columns["EndWork"].HeaderText = "Окончание работы";
            form.dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";
            form.dgview1.Columns["EndWork"].Width = 65;
            
 
        }

        public string CalculateMD5Hash(string input)
        {
            // step 1, calculate MD5 hash from input
            MD5 md5 = System.Security.Cryptography.MD5.Create();
            byte[] inputBytes = System.Text.Encoding.ASCII.GetBytes(input);
            byte[] hash = md5.ComputeHash(inputBytes);


            // step 2, convert byte array to hex string
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < hash.Length; i++)
            {
                sb.Append(hash[i].ToString("x2"));
            }
            return sb.ToString();
        }

        private void cmbBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            
            try
            {
             //   if (Convert.ToBoolean(conn.State) == false) conn.Open();
                if (cmbBox1.CanSelect)
                {
                     
                    //string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name";
                   // string sql = "select  \"Name\",\"Data\",\"StartWork\",\"EndWork\" from \"WorkTime\".\"WorkTimes\", \"WorkTime\".\"Employee\" where \"WorkTime\".\"WorkTimes\".\"id_name\"=@a and \"WorkTime\".\"WorkTimes\".\"id_name\"=\"WorkTime\".\"Employee\".\"id\" and \"Data\"=@b";
                    //string sql = "select  \"Name\", \"Data\", \"StartWork\",\"EndWork\"  from \"WorkTime\".\"Employee\",\"WorkTime\".\"WorkTimes\" where id=" + Convert.ToInt32(Convert.ToString(cmbBox1.SelectedValue)) + "and \"Data\"=\'2013-01-22\'";
                    // string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name";
                    //string sql = "select  *  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=@a";
                    qqq2(this, Global_variables.sql);
                    
                    if (0 != qqq(this, Global_variables.sql4))
                    {
                        
                        btnStartWork.Enabled = false;
                        
                        
                        if (qqq1(this, Global_variables.sql5) != 0)
                        {
                            btnEndWork.Enabled = false;
                            btnStartWork.Enabled = false;
                            

                        }
                        else
                        {
                            btnStartWork.Enabled = false;
                            btnEndWork.Enabled = true;
                            
                        }
                    }
                    else
                    {
                        btnStartWork.Enabled = true;
                        btnEndWork.Enabled = false;
                    }

                    
                   
                }
             //   MessageBox.Show("Passed", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }


            finally
            {
               //conn.Close();
            }
        }

       

        private void btnOK_Click(object sender, EventArgs e)
        {
           
            try
            {
                
               // conn.Open(); //opens the connection
                string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name";
                /*
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                // i always reset DataSet before i do something with it.... i don't know why :-)
                ds = new DataSet();
                ds.Reset();
                // filling DataSet with result from NpgsqlDataAdapter
                
                da.Fill(ds);
                // since it C# DataSet can handle multiple tables, we will select first
                dt = ds.Tables[0];

                //dgview1.AllowUserToAddRows = false;
                //dgview1.Columns[0].DefaultCellStyle.Format = "HH:mm";
                //dgview1.Columns[2].Name = "Asdf";
                //dgview1.DefaultCellStyle.Format = "HH:mm";
                
                // connect grid to DataTable
                //DataGridViewTextBoxColumn buttonColumn = new DataGridViewTextBoxColumn();
                DataColumn dc = new DataColumn("Item", typeof(String));
                dc.ColumnName = "Item";
               // Columns["Разность"].
                dt.Columns.Add(dc);
                //MessageBox.Show(Convert.ToString(dt.Columns[4].DataType));
                

                foreach(DataRow row in dt.Rows)
                {
                    DateTime dateTime1 = Convert.ToDateTime(row.ItemArray[2]);
                    DateTime dateTime2 = new DateTime();
                    if (row.ItemArray[3] != DBNull.Value)
                    {
                        dateTime2 = Convert.ToDateTime(row.ItemArray[3]);
                        TimeSpan aaa = TimeSpan.Zero;
                        aaa = dateTime2 - dateTime1;
                        row["Item"] = string.Format("{0:00}:{1:00}", aaa.TotalHours, aaa.Minutes);  // (TimeSpan)(dateTime2 - dateTime1);
                    }

                }
                string sortString = "[Name] asc, [Data]  asc";
                dt.DefaultView.Sort = sortString;
                
                

                dgview1.AutoGenerateColumns = true;        
                dgview1.DataSource = dt;
                
                
                dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";
                dgview1.Columns["Name"].Width = 110;

                dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Data"].HeaderText = "Дата";
                dgview1.Columns["Data"].Width = 80;

                dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["StartWork"].HeaderText = "Начало";
                dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";
                dgview1.Columns["StartWork"].Width = 50;

                dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["EndWork"].HeaderText = "Окончание";
                dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";
                dgview1.Columns["EndWork"].Width = 60;
                
                dgview1.Columns["Item"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Item"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Item"].HeaderText = "Время работы";
                dgview1.Columns["Item"].Width = 50;
               

               

                dgview1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                dgview1.AllowUserToResizeColumns = false;

               */
                qqq3(this, sql);
                /*for (int i = 0;i<=dgview1.Rows.Count - 1;i++){
                    DateTime dateTime1 = Convert.ToDateTime(dgview1.Rows[i].Cells[2].Value);
                    //MessageBox.Show(Convert.ToString(dateTime1));
                    DateTime dateTime2 = (DateTime)dgview1.Rows[i].Cells[3].Value;

                    dgview1.Rows[i].Cells[4].Value = (TimeSpan)(dateTime2 - dateTime1);
                    
                   
                }*/

              //  dgview1.Columns["Item"].DefaultCellStyle.Format = "HH:mm:ss"

               



/*                if (!dr.Read())
                {
                    MessageBox.Show("Error", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                }
 */
                
           
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }

            finally
            {
                //conn.Close();
            }
        }

        private void btnStartWork_Click(object sender, EventArgs e)
        {
            try
            {
               
                //conn.Open();
                
               // String str1 = cmbBox1.SelectedItem.ToString();
               // string sql2 = "select \"id\" from \"WorkTime\".\"Employee\" where \"Name\"=@Names";
                ////NpgsqlDataAdapter da2 = new NpgsqlDataAdapter(sql2, conn);
                //NpgsqlCommand command = new NpgsqlCommand(sql2, conn);
                //command.Parameters.Add(new NpgsqlParameter("@Names", NpgsqlTypes.NpgsqlDbType.Text));
                ////command.Prepare();
                //command.Parameters[0].Value = str1;
                //NpgsqlDataReader dr = command.ExecuteReader();
/*                    while(dr.Read())
                    {
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            Console.Write("{0} \t", dr[i]);
                            
                        }
                        Console.WriteLine();
                    }
  */
                    //textBox1.Text = Convert.ToString(cmbBox1.SelectedValue);
                    //DateTime currentDate = DateTime.Today;
                    //int year = currentDate.Year;
                    //int month = currentDate.Month;
                    //int day = currentDate.Day;
                    
                    //string curdate = currentDate.ToString("u");
                    //string curDate = curdate.Split(' ')[0];
                    //DateTime aaa = new DateTime(year,month,day);
                    
                    //textBox1.Text = curDate;
                    DateTime curTime = DateTime.Now;
                    //string qqq = Convert.ToString(curTime);
                    //textBox1.Text = Convert.ToString(aaa);
                   /*
                    int year = int.Parse(curdate.Split('-')[0]);
                    int month = int.Parse(curdate.Split('-')[1]);
                    int day = int.Parse(curdate.Split('-')[2]);
                    DateTime time = new DateTime(year, month, day,0,0,0);
                    //textBox1.Text = time.ToString("y-m-d");
                    */
                    //textBox1.Text = curDate.ToString("d");

                    //DateTime ddd = DateTime.Parse(DateNow);
                //    DateTime ddd = DateTime.ParseExact(DateNow, "yyyy-MM-d", null);
                    //DateTime test = DateTime.ParseExact("2004-10-10 10:10:30.120","yyyy-MM-dd hh:mm:ss.fff", null); 
                    
                  //  string dtd = DateTime.Now.ToString();
                    //textBox1.Text = DateNow;
                    //DateTime objdate = Convert.ToDateTime(DateNow);
                    //DateTime myTime = DateTime.ParseExact(DateNow, "yyyy-MM-dd", null);

                   
                   //int count = command4.ExecuteNonQuery();
                   
                    
                /*
                    NpgsqlDataAdapter da = new NpgsqlDataAdapter("select \"id_name\" from \"WorkTime\".\"WorkTimes\" where \"Data\"=\'2012-12-25\'", conn);

                    DataSet ds = new DataSet();
                    ds.Reset();
                    da.Fill(ds);

                    
                    // since it C# DataSet can handle multiple tables, we will select first
                    dt = ds.Tables[0];
                    // connect grid to DataTable
                    dgview1.DataSource = dt;

                */



                //textBox1.Text = Convert.ToString(command4.ExecuteNonQuery());

                   
                        //textBox1.Text = curData.ToString("dd/MM/YY");


                        
                        //textBox1.Text = curTime.ToString("HH:mm");

                        string sql3 = "insert into \"WorkTime\".\"WorkTimes\" (\"id_name\",\"Data\",\"StartWork\")" + "values (@a, @b, @c)";
                        NpgsqlCommand command1 = new NpgsqlCommand(sql3, conn);
                        command1.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Integer));
                        command1.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Date));
                        command1.Parameters.Add(new NpgsqlParameter("@c", NpgsqlTypes.NpgsqlDbType.Time));


                        //int qq = Convert.ToInt32(dr.Read());
                        //command1.Parameters[0].Value = (Int32)cmbBox1.Items[1];
                        curTime = DateTime.Now;
                        command1.Parameters[0].Value = Convert.ToInt32(Convert.ToString(this.cmbBox1.SelectedValue));
                        command1.Parameters[1].Value = Convert.ToDateTime(DateNow);
                        command1.Parameters[2].Value = Convert.ToDateTime(curTime.ToString("HH:mm"));
                        command1.ExecuteNonQuery();
                        //NpgsqlDataReader dr1 = command1.ExecuteReader();
                        btnEndWork.Enabled = true;
                    
                       // MessageBox.Show("QQQQQQQ", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                        string sql = "select  \"Name\",\"Data\",\"StartWork\",\"EndWork\" from \"WorkTime\".\"WorkTimes\", \"WorkTime\".\"Employee\" where \"WorkTime\".\"WorkTimes\".\"id_name\"=@a and \"WorkTime\".\"WorkTimes\".\"id_name\"=\"WorkTime\".\"Employee\".\"id\" and \"Data\"=@b";
                        NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                        da.SelectCommand.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Integer));
                        da.SelectCommand.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Date));
                        da.SelectCommand.Parameters[0].Value = int.Parse(cmbBox1.SelectedValue.ToString());
                        da.SelectCommand.Parameters[1].Value = Convert.ToDateTime(DateNow);
                        ds.Reset();
                        da.Fill(ds);
                        dt = ds.Tables[0];
                        dgview1.DataSource = dt;

                        //textBox1.Text = cmbBox1.Text;

                        dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";

                        dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["Data"].HeaderText = "Дата";

                        dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["StartWork"].HeaderText = "Начало работы";
                        dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";

                        dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                        dgview1.Columns["EndWork"].HeaderText = "Окончание работы";
                        dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";
                         

                }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }

            finally
            {
               // conn.Close();
                btnStartWork.Enabled = false;
            }
            
            
          //  
        }

        private void btnEndWork_Click(object sender, EventArgs e)
        {
           // conn.Open();
            try
            {
                curTime = DateTime.Now;
                string sql3 = "update \"WorkTime\".\"WorkTimes\" set \"EndWork\"=@a where id_name=@b";
                NpgsqlCommand command2 = new NpgsqlCommand(sql3, conn);
                command2.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Time));
                command2.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Integer));
                command2.Parameters[0].Value = Convert.ToDateTime(curTime.ToString("HH:mm"));
                command2.Parameters[1].Value = Convert.ToInt32(Convert.ToString(this.cmbBox1.SelectedValue));
                command2.ExecuteNonQuery();

                //string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name and \"WorkTime\".\"WorkTimes\".\"Data\"=@a";
                string sql = "select  \"WorkTime\".\"Employee\".\"Name\", \"WorkTime\".\"WorkTimes\".\"Data\", \"WorkTime\".\"WorkTimes\".\"StartWork\",\"WorkTime\".\"WorkTimes\".\"EndWork\"  from  \"WorkTime\".\"Employee\" join \"WorkTime\".\"WorkTimes\" on \"WorkTime\".\"Employee\".id=\"WorkTime\".\"WorkTimes\".id_name and \"WorkTime\".\"WorkTimes\".\"Data\"=@a and \"WorkTime\".\"WorkTimes\".id_name=@b";
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.Add(new NpgsqlParameter("@a", NpgsqlTypes.NpgsqlDbType.Date));
                da.SelectCommand.Parameters.Add(new NpgsqlParameter("@b", NpgsqlTypes.NpgsqlDbType.Integer));
                da.SelectCommand.Parameters[0].Value = Convert.ToDateTime(curTime);
                da.SelectCommand.Parameters[1].Value = int.Parse(cmbBox1.SelectedValue.ToString());
                

                // i always reset DataSet before i do something with it.... i don't know why :-)
                ds = new DataSet();
                ds.Reset();
                // filling DataSet with result from NpgsqlDataAdapter

                da.Fill(ds);
                // since it C# DataSet can handle multiple tables, we will select first
                dt = ds.Tables[0];

              
                DataColumn dc = new DataColumn("Item", typeof(String));
                dc.ColumnName = "Item";
               
                dt.Columns.Add(dc);
               

                foreach (DataRow row in dt.Rows)
                {
                    DateTime dateTime1 = Convert.ToDateTime(row.ItemArray[2]);
                    DateTime dateTime2 = new DateTime();
                    if (row.ItemArray[3] != DBNull.Value)
                    {
                        dateTime2 = Convert.ToDateTime(row.ItemArray[3]);
                        TimeSpan aaa = TimeSpan.Zero;
                        aaa = dateTime2 - dateTime1;
                        row["Item"] = string.Format("{0:00}:{1:00}", aaa.TotalHours, aaa.Minutes);  // (TimeSpan)(dateTime2 - dateTime1);
                    }

                }
                dgview1.AutoGenerateColumns = true;
                dgview1.DataSource = dt;


                dgview1.Columns["Name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Name"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Name"].HeaderText = "Фамилия И.О.";

                dgview1.Columns["Data"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Data"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Data"].HeaderText = "Дата";

                dgview1.Columns["StartWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["StartWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["StartWork"].HeaderText = "Начало работы";
                dgview1.Columns["StartWork"].DefaultCellStyle.Format = "HH:mm";

                dgview1.Columns["EndWork"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["EndWork"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["EndWork"].HeaderText = "Окончание работы";
                dgview1.Columns["EndWork"].DefaultCellStyle.Format = "HH:mm";

                dgview1.Columns["Item"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Item"].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
                dgview1.Columns["Item"].HeaderText = "Время работы";




                dgview1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
                dgview1.AllowUserToResizeColumns = false;
            }
            catch (Exception msg)
            {
                // something went wrong, and you wanna know why
                MessageBox.Show(msg.ToString());
                throw;
            }

            finally
            {
              //  conn.Close();
                btnEndWork.Enabled = false;
            }
        }

       

        
    }
}
